/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package connect;
//package com.datos;

import java.util.*;
import java.sql.*;

public class HandlerDB {

   // private final String driver = "org.postgresql.Driver";
    private final String driver = "com.mysql.jdbc.Driver"; //2


    //para realizar de forma local con mySql
    private final String url = "jdbc:mysql://localhost/vclass_delete";
    private final String user = "root";
    private final String pasw = "";
/*
    //Para el Host con mysql //2
    private final String url= "jdbc:mysql://localhost/rmirc20_virtualclass";
    private final String user="rmirc20";
    private final String pasw="1qaz2wsx";
*/
/*
    //para realizar de forma local con postgres
    private final String url = "jdbc:postgresql://localhost/virtualclass";
    private final String user = "postgres";
    private final String pasw = "postgres";
*/

    /*
    //Para el Host con postgres
    private final String url= "jdbc:postgresql://localhost/rmirc20_virtualclass";
    private final String user   = "rmirc20";
    private final String pasw   = "1qaz2wsx";
    */


    private Connection db;

    public HandlerDB() {
    }

    public int openConnection() {
        try {
            Class.forName(driver).newInstance();
            this.db = DriverManager.getConnection(url, user, pasw);
            return 1;
        } catch (Exception e) {
            e.printStackTrace();
            return 0;
        }
    }
    // cierra la conexion a la BD

    public void closeConnection() {
        try {
            // cierra la conexion
            this.db.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    // convierte un ResultSet a un Vector
    private Vector ResultSetToVector(ResultSet rs) throws SQLException {
        Vector vRows = new Vector();
        // lee registro por registro
        while (rs.next()) {
            Vector vCol = new Vector();
            // lee cada campo del query
            int nroFields = rs.getMetaData().getColumnCount();
            for (int i = 1; i <= nroFields; i++) {
                String strTmp = rs.getString(i);
                vCol.add(strTmp);
            }
            vRows.add(vCol);
        }


        return vRows;
    }
    // ejecuta delete,insert ó update

    public void performance(String str) {
        try {
            // crea el statement
            Statement stmt = this.db.createStatement();
            // ejecuta el update
            stmt.executeUpdate(str);
            //cierra statement
            stmt.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    public void performance(String str, String aParam[]) {
        PreparedStatement pstm;
        try {
            // crea el statement
            pstm = this.db.prepareStatement(str);
            // ejecuta el update
            for (int xc = 0; xc < aParam.length; xc++) {
                pstm.setString(xc + 1, aParam[xc]);
            }
            pstm.executeUpdate();
            //cierra statement
            pstm.close();
        } catch (SQLException e) {

            e.printStackTrace();
        }
    }
    // ejecuta solo los query

    public Vector query(String strQuery) {
        Vector vRet = null;
        try {
            // crea el statement
            Statement stmt = this.db.createStatement();
            // ejecuta el query
            ResultSet rs = stmt.executeQuery(strQuery);
            // convierte el ResultSet to Vector
            vRet = ResultSetToVector(rs);
            // cierra el ResultSet
            rs.close();
            // cierra el statement
            stmt.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return vRet;
    }

    public Vector query(String strQuery, String xcod) {
        Vector vRet = null;
        try {
            // crea el statement
            PreparedStatement pstm = this.db.prepareStatement(strQuery);
            // Inicializa parametros
            pstm.setString(1, xcod);
            // ejecuta el query
            ResultSet rs = pstm.executeQuery();
            // convierte el ResultSet to Vector
            vRet = ResultSetToVector(rs);
            // cierra el ResultSet
            rs.close();
            // cierra el statement
            pstm.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return vRet;
    }

    public void sp_proc(String sp_name, String aParam[]) {
        try {
            CallableStatement cs = this.db.prepareCall(sp_name);
            for (int xc = 0; xc < aParam.length; xc++) {
                cs.setString(xc + 1, aParam[xc]);
            }
            cs.execute();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    public void sp_proc(String sp_name, String aParam) {
        try {
            CallableStatement cs = this.db.prepareCall(sp_name);
            cs.setString(1, aParam);
            cs.execute();
        } catch (Exception e) {
            e.printStackTrace();
        }

    }
    // ejecuta un delete

    public void delete(String strDelete) {
        performance(strDelete);
    }
    // ejecuta un update

    public void update(String strUpdate) {
        performance(strUpdate);
    }
    // ejecuta un insert

    public void insert(String strInsert) {
        performance(strInsert);
    }

    public void delete(String strDelete, String aParam[]) {
        performance(strDelete, aParam);
    }

    public void update(String strUpdate, String aParam[]) {
        performance(strUpdate, aParam);
    }

    public void insert(String strInsert, String aParam[]) {
        performance(strInsert, aParam);
    }
}
